package com.edu.hbwe.book.dao.impl;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.edu.hbwe.book.dao.ProductDao;
import com.edu.hbwe.book.entity.Order;
import com.edu.hbwe.book.entity.OrderItem;
import com.edu.hbwe.book.entity.Product;
import com.edu.hbwe.book.util.DbUtil;


public class ProductDaoImpl implements ProductDao{
	// 添加商品
	public int addProduct(Product p)  {

		String sql = "insert into products values(?,?,?,?,?,?,?)";
		QueryRunner runner = new QueryRunner(DbUtil.getDataSource());
		int row = 0;
		try {
			row = runner.update(sql, p.getId(), p.getName(), p.getPrice(),
					p.getCategory(), p.getPnum(), p.getImgurl(), p.getDescription());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return row;
	}
	// 查找所有商品
	public List<Product> listAll()  {
		String sql = "select * from products";
		QueryRunner runner = new QueryRunner(DbUtil.getDataSource());
		List<Product> list = null;
		try {
			list = runner.query(sql, new BeanListHandler<Product>(Product.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	// 获取数据总条数
	public int findAllCount(String category)  {
		String sql = "select count(*) from products";

		QueryRunner runner = new QueryRunner(DbUtil.getDataSource());

			Long count = 0L;
			try {
				
				if (!"全部商品".equals(category)) {
					sql += " where category=?";
				count = (Long) runner
						.query(sql, new ScalarHandler(), category);
				} else {
					
					try {
						count = (Long) runner.query(sql, new ScalarHandler());
					} catch (SQLException e) {
						e.printStackTrace();
					}
					
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return count.intValue();
	}
	// 获取当前页数据
	public List<Product> findByPage(int currentPage, int currentCount,
			String category)  {
		// 要执行的sql语句
		String sql = null;
		// 参数
		Object[] obj = null;
		// 如果category不为null,代表是按分类查找
		if (!"全部商品".equals(category)) {
			sql = "select * from products  where category=? limit ?,?";
			obj = new Object[] { category, (currentPage - 1) * currentCount,
					currentCount, };
		} else {
			sql = "select * from products  limit ?,?";
			obj = new Object[] { (currentPage - 1) * currentCount,
					currentCount, };
		}
		QueryRunner runner = new QueryRunner(DbUtil.getDataSource());
		 List<Product> list =null;
		try {
			list = runner.query(sql, new BeanListHandler<Product>(Product.class),
						obj);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	// 根据id查找商品
	public Product findProductById(String id)  {
		String sql = "select * from products where id=?";
		QueryRunner runner = new QueryRunner(DbUtil.getDataSource());
		Product product = null;
		try {
			product = runner.query(sql, new BeanHandler<Product>(Product.class), id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return product ; 
	}


	// 销售榜单
	public List<Object[]> salesList(String year, String month)
			 {
		String sql = "SELECT products.name,SUM(orderitem.buynum) totalsalnum FROM orders,products,orderItem WHERE orders.id=orderItem.order_id AND products.id=orderItem.product_id AND orders.paystate=1 and year(ordertime)=? and month(ordertime)=? GROUP BY products.name ORDER BY totalsalnum DESC";
		QueryRunner runner = new QueryRunner(DbUtil.getDataSource());
		List<Object[]> list = null;
		 try {
			list = runner.query(sql, new ArrayListHandler(), year, month);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list ;
	}
	//获取本周热卖商品
	public List<Object[]> getWeekHotProduct()  {
		String sql = "SELECT products.id,products.name, "+
				" products.imgurl,SUM(orderitem.buynum) totalsalnum "+
				" FROM orderitem,orders,products "+
				" WHERE orderitem.order_id = orders.id "+
				" AND products.id = orderitem.product_id "+
				" AND orders.paystate=1 "+
				" AND orders.ordertime > DATE_SUB(NOW(), INTERVAL 7 DAY) "+
				" GROUP BY products.id,products.name,products.imgurl "+
				" ORDER BY totalsalnum DESC "+
				" LIMIT 0,2 ";
		QueryRunner runner = new QueryRunner(DbUtil.getDataSource());
		List<Object[]> list = null;
		try {
			list = runner.query(sql, new ArrayListHandler());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list; 
	}
	//后台系统，根据id删除商品信息
	public int deleteProduct(String id) {
		String sql = "DELETE FROM products WHERE id = ?";
		QueryRunner runner = new QueryRunner(DbUtil.getDataSource());
		int row =0;
		try {
			row = runner.update(sql, id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return row;
	}

	public int findBookByNameAllCount(String searchfield)  {
		String sql = "SELECT COUNT(*) FROM products WHERE name LIKE '%"+searchfield+"%'";
		QueryRunner runner = new QueryRunner(DbUtil.getDataSource());
		//查询出满足条件的总数量，为long类型
		Long count = 0L;
		try {
			count = (Long)runner.query(sql, new ScalarHandler());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count.intValue();
	}

	public List<Product> findBookByName(int currentPage, int currentCount, String searchfield)  {
		//根据名字模糊查询图书
		String sql = "SELECT * FROM products WHERE name LIKE '%"+searchfield+"%' LIMIT ?,?";
		QueryRunner runner = new QueryRunner(DbUtil.getDataSource());
		List<Product> query =null;
		try {
			 query = runner.query(sql, 
					new BeanListHandler<Product>(Product.class),currentPage-1,currentCount);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return query;
	}
	public List<Product> findProductByManyCondition(String id, String name, String category, String minprice,
			String maxprice)  {
		//1.准备sql
		String sql="select * from products where 1=1";
		//2.拼接sql,给?复制
		List<Object> list=new ArrayList<Object>();
		if (id!=null && id.trim().length()>0) {
			sql +=" and id=?";
			list.add(id); 
		}
		if (name!=null && name.trim().length()>0) {
			sql +=" and name=?";
			System.out.println(name);
			list.add(name);
		}
		if (category!=null && category.trim().length()>0) {
			sql +=" and category=?";
			list.add(category);
		}
		if (minprice!=null && maxprice!=null && minprice.trim().length()>0 && maxprice.trim().length()>0) {

			sql +=" and price between ? and ?";
			list.add(minprice);
			list.add(maxprice);
		}
		QueryRunner runner=new QueryRunner(DbUtil.getDataSource());
		Object[] params=list.toArray();
		List<Product> listpProducts =null;
		try {
			listpProducts = runner.query(sql, new BeanListHandler<Product>(Product.class), params);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return listpProducts;

	}
	@Override
	public int editProduct(Product p)  {
		//1.创建集合并将商品信息添加到集合中
		List<Object> obj = new ArrayList<Object>();
		obj.add(p.getName());
		obj.add(p.getPrice());
		obj.add(p.getCategory());
		obj.add(p.getPnum());
		obj.add(p.getDescription());
		//2.创建sql语句，并拼接sql
		String sql  = "update products " +
				"set  name=?,price=?,category=?,pnum=?,description=? ";
		//判断是否有图片
		if (p.getImgurl() != null && p.getImgurl().trim().length() > 0) {
			sql += " ,imgurl=?";
			obj.add(p.getImgurl());
		}
		sql += " where id=?";
		obj.add(p.getId());		
		//3.创建QueryRunner对象
		QueryRunner runner = new QueryRunner(DbUtil.getDataSource());
		//4.使用QueryRunner对象的update()方法更新数据
		int row =0;
		try {
			row = runner.update(sql, obj.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return row;
	}
	@Override
	public void changeProductNum(Order order) throws SQLException {
		String sql = "update products set pnum=pnum-? where id=?";
		QueryRunner runner = new QueryRunner();
		List<OrderItem> items = order.getOrderItems();
		Object[][] params = new Object[items.size()][2];

		for (int i = 0; i < params.length; i++) {
			params[i][0] = items.get(i).getBuynum();
			params[i][1] = items.get(i).getP().getId();
		}

		runner.batch(DbUtil.getConnection(), sql, params);
	}













}
